from django.core.exceptions import ObjectDoesNotExist
from django.core.paginator import Paginator
from django.db import connection
from django.http import HttpResponse
from django.http import HttpResponseRedirect
from django.shortcuts import render

from .models import Book, Author, Wife, Publisher


# Create your views here.
def init_books(request):
    Book.objects.create(title='C', price=30, market_price=35, pub_house='清华大学出版社')
    Book.objects.create(title='C++', price=40, market_price=45, pub_house='清华大学出版社')
    Book.objects.create(title='Java', price=50, market_price=55, pub_house='清华大学出版社')
    Book.objects.create(title='Python', price=20, market_price=25, pub_house='清华大学出版社')
    Book.objects.create(title='Python3', price=60, market_price=65, pub_house='清华大学出版社')
    Book.objects.create(title='Django', price=70, market_price=75, pub_house='清华大学出版社')
    Book.objects.create(title='JQuery', price=90, market_price=85, pub_house='机械工业出版社')
    Book.objects.create(title='Linux', price=80, market_price=65, pub_house='清华大学出版社')
    Book.objects.create(title='Windows', price=50, market_price=35, pub_house='机械工业出版社')
    Book.objects.create(title='HTML5', price=90, market_price=105, pub_house='机械工业出版社')
    return HttpResponse('初始化成功')


def add_books(request):
    if request.method == "GET":
        title = request.GET.get('title', 'None')
        price = request.GET.get('price', 0)
        market_price = request.GET.get('marketPrice', 0)
        pub_house = request.GET.get('pubHouse', 'None')
        Book.objects.create(title=title, price=price, market_price=market_price, pub_house=pub_house)
        return HttpResponse('创建成功')

    elif request.method == 'POST':
        return HttpResponse('该方法还没创建')

    return HttpResponse('创建失败')


def add_book(request):
    if request.method == 'GET':
        return render(request, 'bookstore/add.html')
    elif request.method == 'POST':
        title = request.POST.get('title', '')
        price = request.POST.get('price', '0')
        price = price if price else '0'
        price = float(price)
        pub_house = request.POST.get('pubHouse', '')
        pub_date = request.POST.get('pubDate', '')
        market_price = request.POST.get('marketPrice', '0')
        Book.objects.create(title=title, price=price, market_price=market_price, pub_house=pub_house, pub_date=pub_date)

        # 添加数据方式  方法二
        # abook = Book()
        # abook.title = title
        # abook.price = price
        # abook.pub_house = pub_house
        # abook.pub_date = pub_date
        # abook.market_price = market_price
        # abook.save()

        status = '创建成功'
        return HttpResponse('<a href="/book/home">添加新书成功，点我跳转到首页</a>')


def home_page(request):
    return render(request, 'bookstore/index.html')


def list_books(request):
    # 从模型中取数据
    # books = Book.objects.all()
    # books = Book.objects.order_by('price')
    paginator = Paginator(Book.objects.raw('select * from bookstore_book order by price'), 10)
    print('当前对象的总个数是:', paginator.count)
    print('当前对象的页码范围是:', paginator.page_range)
    print('总页数是:', paginator.num_pages)
    print('每页最大个数:', paginator.per_page)
    # 得到默认的当前页
    cur_page = request.GET.get('page', 1)
    books = paginator.page(cur_page)
    return render(request, 'bookstore/list.html', locals())


def filter_books(request):
    # books = Book.objects.filter(pub_house='清华大学出版社')
    books = Book.objects.filter(price__gt=50)
    # __gte   大于等于
    # __exact 等于
    # __contains 包含指定值  '%指定值%'
    # __startswith 以xxx开始
    # __endswith 以xxx结束
    # __lt 小于
    # __lte
    # __in 查找数据是否再指  定范围
    # __range 查找数据是否在指定范围
    # objects.filter(xxx|xxx) 或
    # objects.filter(xxx & xxx) 且  或者使用逗号“,”表示且 objects.filter(xxx,xxx)
    # Book.objects.exclude(price__gte=80,) 除以外数据

    # 聚合函数
    # from django.db.models import Avg, Count, Max, Min, Sum
    # # 不带分组
    # result = Book.objects.aggregate(myAvg=Avg('price'))
    # # print(result)
    # # {"myAvg":50.8}
    # # 带分组
    # pub_set = Book.objects.values('pub')
    # # QuerySet[{pub:...}]
    # pub_set.annotate(myCount=Count('pub'))
    return render(request, 'bookstore/list.html', locals())


def update(request, book_id):
    """
        更新图书信息
    """
    try:
        book = Book.objects.get(id=book_id)
    except:
        return HttpResponse('没有找到ID为' + book_id + '的图书信息')

    if request.method == 'GET':
        return render(request, 'bookstore/update.html', locals())
    elif request.method == 'POST':
        try:
            m_price = request.POST.get('marketPrice', '0.0')
            book.market_price = m_price
            book.save()
            return HttpResponse('修改成功<br/>' + '<a href="/book/home">添加新书成功，点我跳转到首页</a>')
        except:
            return HttpResponse('修改失败<br/>' + '<a href="/book/home">添加新书成功，点我跳转到首页</a>')


def delete(request, book_id):
    """
        django connection.cursor()使用
    """
    try:
        # book = Book.objects.get(id=book_id)
        # raw更适合查询操作，更新、删除操作使用游标cursor
        # books = Book.objects.raw('select * from bookstore_book where id = %s', [book_id])
        # for book in books:
        #     book.delete()
        with connection.cursor() as cur:
            cur.execute('delete from bookstore_book where id=%s', [book_id])
        return HttpResponseRedirect('/book/list')
    except Exception as ex:
        print(ex)
        return HttpResponse('没有找到ID为' + book_id + '的图书信息，删除失败')


def author_marry(request):
    """
        一对一数据初始化
    """
    try:
        author_bei_lin = Author.objects.create(name='撒贝林2', age=47, email='3242442@qq.com')
        Wife.objects.create(name='李白2', age=31, author=author_bei_lin)
        author_si_cong = Author.objects.create(name='王思聪2', age=37, email='888888888@qq.com')
    except Exception as ex:
        print(ex)

    return HttpResponse('创建成功')


def query_wife_husband(request, wife_id):
    """
        查找妻子丈夫信息(一对一正向查找)
    """
    try:
        wife = Wife.objects.get(id=wife_id)
        return HttpResponse(wife.name + '的老公是' + wife.author.name)
    except:
        return HttpResponse('找到不id=%s的此人信息' % wife_id)


def query_author_wife(request, author_id):
    """
        查找作者妻子信息(一对一反向查找)
    """
    try:
        author = Author.objects.get(id=author_id)
        wife = getattr(author, "wife", None)
        if not wife:
            return HttpResponse('%s还没取老婆呢' % author.name)
        return HttpResponse(author.name + '的老婆是' + author.wife.name)
    except ObjectDoesNotExist as ex:

        return HttpResponse('找到不id=%s的此人信息' % author_id)


def create_book_by_sys(request):
    """
        图书出版社一对多数据初始化
    """
    pub1 = Publisher.objects.create(name="清华大学出版社")
    Book.objects.create(title="SQL Server从入门到精通", publisher=pub1)
    Book.objects.create(title="HTML5+CSS3+JAVASCRIPT", publisher=pub1)
    Book.objects.create(title="TYPESCRIPT", publisher=pub1)

    pub2 = Publisher.objects.create(name="机械工业出版社")
    Book.objects.create(title="C语言从入门到精通", publisher=pub2)
    Book.objects.create(title="安卓开发", publisher=pub2)
    return HttpResponse('创建图书信息关联出版社成功')


def get_book(request, book_id):
    """
    通过图书找到出版社信息
    """
    try:
        book = Book.objects.get(id=book_id)
        if not getattr(book, "publisher", None):
            return HttpResponse('%s图书找不到出版社' % book.title)
        return HttpResponse(book.title + '的图书出版社是:' + book.publisher.name)
    except:
        return HttpResponse('没有找到id=%s的书' % book_id)


def list_book(request, publisher_id):
    """
        通过出版社查找图书信息
    """
    publisher = Publisher.objects.create(id=publisher_id)
    # 通过book_set获取出版社的多个book数据对象
    books = publisher.book_set.all()
    book_list = ""
    for book in books:
        book_list += book.title + "\n"
    return HttpResponse(book_list)


def book_author_many_init(request):
    """
        初始化多对多数据
    """
    # 韩立刚和马士兵共同创建高等数学
    author1 = Author.objects.create(name='韩立刚', age=47)
    author2 = Author.objects.create(name='马士兵', age=41)
    book1 = author1.book_set.create(title='高等数学', price=90, market_price=100, pub_house='清华大学出版社')
    author2.book_set.add(book1)

    author1.book_set.create(title='计算机网络原理与应用', price=80, market_price=60, pub_house='机械工业出版社')
    author1.book_set.create(title='windows从入门到精通', price=50, market_price=30, pub_house='机械工业出版社')
    return HttpResponse('初始化成功')


def show_many2many(request):
    """
        查看多对多数据
    """
    authors = Author.objects.all()
    author_books = ''
    book_authors = ''
    for auth in authors:
        author_books += '作者:' + auth.name + '发版了' + str(auth.book_set.count()) + '本书<br/>'
    books = Book.objects.all()
    for book in books:
        auths = book.author.all()
        book_authors += book.title + '的作者是:' + '、'.join([str(x.name) for x in auths]) + '<br/>'
    return HttpResponse(author_books + '<br/>' + book_authors)
